var ConnectionString = "Server=sympc33;Database=Airflow;User Id=sa;Password=sqladmin";
var database = new Database();
var connection = database.OpenDatabaseConnection(ConnectionString);
var id1, id2, id3, id4, id5, id6, id7, id8, id9, id10, id11, id12, id13, id14, id15, id16, id17, id18, id19, id20;
function OnServerStart() {
  var ob = "server";
  id1 = SubscribeToTag("Boolean1", ob, OnTagValueChange);
  id2 = SubscribeToTag("Boolean2", ob, OnTagValueChange);
  id3 = SubscribeToTag("Boolean3", ob, OnTagValueChange);
  id4 = SubscribeToTag("Boolean4", ob, OnTagValueChange);
  id5 = SubscribeToTag("Sine1", ob, OnTagValueChange);
  id6 = SubscribeToTag("Sine2", ob, OnTagValueChange);
  id7 = SubscribeToTag("Sine3", ob, OnTagValueChange);
  id8 = SubscribeToTag("Sine4", ob, OnTagValueChange);
  id9 = SubscribeToTag("Ramp1", ob, OnTagValueChange);
  id10 = SubscribeToTag("Ramp2", ob, OnTagValueChange);
  id11 = SubscribeToTag("Ramp3", ob, OnTagValueChange);
  id12 = SubscribeToTag("Ramp4", ob, OnTagValueChange);
  id13 = SubscribeToTag("Random1", ob, OnTagValueChange);
  id14 = SubscribeToTag("Random2", ob, OnTagValueChange);
  id15 = SubscribeToTag("Random3", ob, OnTagValueChange);
  id16 = SubscribeToTag("Random4", ob, OnTagValueChange);
  id17 = SubscribeToTag("User1", ob, OnTagValueChange);
  id18 = SubscribeToTag("User2", ob, OnTagValueChange);
  id19 = SubscribeToTag("User3", ob, OnTagValueChange);
  id20 = SubscribeToTag("User4", ob, OnTagValueChange);
//  SubscribeToTag("aaa", ob, OnTagValueChange);
}
				
function OnServerStop() {
  UnSubscribeToTag(id1);
  UnSubscribeToTag(id2);
  UnSubscribeToTag(id3);
  UnSubscribeToTag(id4);
  UnSubscribeToTag(id5);
  UnSubscribeToTag(id6);
  UnSubscribeToTag(id7);
  UnSubscribeToTag(id8);
  UnSubscribeToTag(id9);
  UnSubscribeToTag(id10);
  UnSubscribeToTag(id11);
  UnSubscribeToTag(id12);
  UnSubscribeToTag(id13);
  UnSubscribeToTag(id14);
  UnSubscribeToTag(id15);
  UnSubscribeToTag(id16);
//UnSubscribeToTag(id17);
//UnSubscribeToTag(id18);
//UnSubscribeToTag(id19);
//UnSubscribeToTag(id20);

//UnSubscribeToTag(1000);
}
 
var c = 1;
function OnTagValueChange(tagName, id, value, quality, theObject) {
  WriteToDB(tagName, id, value, quality, theObject, c);
  c++;
 
  if (tagName == "Boolean1" && value == 1) {
    SendEmail();
  }

  if (c == 100) {
    UnSubscribeToTag(id17);
    UnSubscribeToTag(id18);
    UnSubscribeToTag(id19);
    UnSubscribeToTag(id20);
  }
}

function SendEmail() {
  LogInfo("SendEmail");
 
  var client = new System.Net.Mail.SmtpClient("smtp.gmail.com", 587);
  client.Credentials = new System.Net.NetworkCredential("[email protected]", "alexzhangtest");
  client.EnableSsl = true;
 
  var message = new System.Net.Mail.MailMessage();
  message.From = new System.Net.Mail.MailAddress("[email protected]");
  message.To.Add(new System.Net.Mail.MailAddress("[email protected]"));
//message.CC.Add(new System.Net.Mail.MailAddress("[email protected]"));
  message.Subject = "sent from macro";
  message.Body = "Boolean1 is true!!!";
 
  client.Send(message);
 
  LogInfo("SendEmail done");
}

function WriteToDB(tagName, id, value, quality, theObject, c) {
  if (connection == null) return;
  var command = "insert into TestTagValue (Name, Value, Quality, subID, Count) values (";
  if (theObject == null)
    command += GetSqlString(tagName);
  else
   command += GetSqlString(tagName + theObject);
  command += ",";
  command += GetSqlString(value);
  command += ",";
  command += GetSqlString(quality);
  command += ",";
  command += id;
  command += ",";
  command += c;
  command += ")";
  database.ExecuteCommand(connection, command);
}
				
function GetSqlString(value) {
  return "'" + value + "'";
}

function Database() {
  this.OpenDatabaseConnection = function (connectionStr) {
  try {
    LogInfo("Try to connect to " + connectionStr)
    var connection = new System.Data.SqlClient.SqlConnection(connectionStr);
    LogInfo("C: " + connection);
    connection.Open();
    LogInfo("Connected to database: " + connection);
    return connection;
  }
  catch (err) { LogError("Connection Error: " + err); }
  return null;
}

this.ExecuteCommand = function (connection, command) {
  if (connection == null) return;
  try {
//LogInfo("ExecuteCommand: " + command)
    var cmd = new System.Data.SqlClient.SqlCommand(command, connection);
    cmd.ExecuteNonQuery();
  }
  catch (err) { LogError("ExecuteCommand error: " + err); }
  }
}